package dao.impl.LibraryDaoImpl;

import common.util.DBUtil;
import dao.LibraryDao.BorrowDao;
import common.model.Entity.libraryEntity.Borrow;
import common.model.Entity.libraryEntity.enums.BorrowStatus;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

/**
 * Borrow 的实现类
 * 实现了对 t_borrow_record 表的增删改查
 */
public class BorrowDaoImpl implements BorrowDao {
    private static final Logger logger = LoggerFactory.getLogger(BorrowDaoImpl.class);
    private final QueryRunner queryRunner;

    public BorrowDaoImpl() {this.queryRunner = new QueryRunner();};
    public BorrowDaoImpl(QueryRunner queryRunner) {
        this.queryRunner = queryRunner;
    }

    @Override
    public Optional<Borrow> findById(Long aLong) {
        return Optional.empty();
    }

    @Override
    public List<Borrow> findAll() {
        return List.of();
    }

    private void setBorrowSql(Borrow borrow, PreparedStatement ps) throws SQLException {
        ps.setLong(1, borrow.getItemId());
        ps.setLong(2, borrow.getUserId());

        // 日期：今天
        ps.setDate(3, java.sql.Date.valueOf(LocalDate.now()));

        // 状态：BORROWED
        ps.setString(4, BorrowStatus.BORROWED.toString());
    }

    @Override
    public boolean save(Borrow borrow) {
        /* 1. 插入借阅记录 */
        String insertBorrowSql =
                "INSERT INTO t_borrow_record (item_id,user_id,borrow_date,status) " +
                        "VALUES (?,?,?,?)";

        /* 2. 更新副本状态 */
        String updateItemSql =
                "UPDATE t_book_item SET status = 'BORROWED' WHERE id = ?";

        /* 3. 更新图书可借余量 */
        String updateBookSql =
                "UPDATE t_book b JOIN t_book_item i ON b.isbn = i.isbn " +
                        "SET b.available = b.available - 1 " +
                        "WHERE i.id = ? AND b.available > 0";

        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false);

            /* ---------- 1. 写 borrow_record ---------- */
            long borrowId;
            try (PreparedStatement ps = conn.prepareStatement(insertBorrowSql, Statement.RETURN_GENERATED_KEYS)) {
                setBorrowSql(borrow, ps);
                if (ps.executeUpdate() == 0) {
                    conn.rollback();
                    throw new SQLException("创建借阅记录失败，未插入任何行。");
                }
                try (ResultSet rs = ps.getGeneratedKeys()) {
                    if (rs.next()) {
                        borrowId = rs.getLong(1);
                        borrow.setId(borrowId);
                    } else {
                        conn.rollback();
                        throw new SQLException("创建借阅记录失败，未获取到ID。");
                    }
                }
            }

            /* ---------- 2. 更新副本状态 ---------- */
            try (PreparedStatement ps = conn.prepareStatement(updateItemSql)) {
                ps.setLong(1, borrow.getItemId());
                if (ps.executeUpdate() == 0) {
                    conn.rollback();
                    throw new SQLException("更新副本状态失败，可能副本不存在。");
                }
            }

            /* ---------- 3. 扣减图书可借余量 ---------- */
            try (PreparedStatement ps = conn.prepareStatement(updateBookSql)) {
                ps.setLong(1, borrow.getItemId());
                if (ps.executeUpdate() == 0) {
                    conn.rollback();
                    throw new SQLException("库存扣减失败，可能库存已为零。");
                }
            }

            conn.commit();
            return true;

        } catch (SQLException e) {
            logger.error("保存借阅记录失败: {}", e.getMessage(), e);
            return false;
        }
    }

    @Override
    public List<Borrow> findActiveBorrowByUser(Long userId) {
        String sql = "SELECT br.id, br.item_id, br.user_id, br.borrow_date, br.due_date, br.return_date, br.status, " +
                "b.title, b.isbn " +
                "FROM t_borrow_record br " +
                "JOIN t_book_item bi ON br.item_id = bi.id " +
                "JOIN t_book b ON bi.isbn = b.isbn " +
                "WHERE br.user_id = ? AND br.status = 'BORROWED'";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Borrow.class), userId);
        } catch (SQLException e) {
            logger.error("查询用户活跃借阅失败, userId={}", userId, e);
            return Collections.emptyList();
        }
    }

    @Override
    public boolean update(Borrow borrow) {
        // 更新语句：仅更新表中存在的return_date和status字段
        String updateBorrowSql =
                "UPDATE t_borrow_record " +
                        "SET return_date = ?, status = ? " +
                        "WHERE id = ?";

        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false);
            try (PreparedStatement ps = conn.prepareStatement(updateBorrowSql)) {
                // 处理LocalDateTime到SQL Date的转换（仅取日期部分）
                LocalDate returnDateTime = borrow.getReturnDate();
                if (returnDateTime != null) {
                    // 转换为java.sql.Date（只保留日期部分）
                    ps.setDate(1, java.sql.Date.valueOf(returnDateTime));
                } else {
                    ps.setNull(1, java.sql.Types.DATE);
                }

                // 设置状态（确保getStatus()不为null）
                BorrowStatus status = borrow.getStatus();
                if (status != null) {
                    // 注意：这里使用setInt而不是setString，因为状态码是整数
                    ps.setInt(2, status.getCode());
                } else {
                    ps.setNull(2, java.sql.Types.INTEGER);
                }

                // 设置ID（必须存在，否则无法定位记录）
                ps.setLong(3, borrow.getId());

                int affectedRows = ps.executeUpdate();
                if (affectedRows == 0) {
                    conn.rollback();
                    throw new SQLException("更新借阅记录失败，未找到ID为" + borrow.getId() + "的记录");
                }

                conn.commit();
                return true;
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        } catch (SQLException e) {
            logger.error("更新借阅记录失败: {}", e);
            return false;
        }
    }

    @Override
    public boolean deleteById(Long aLong) {
        return false;
    }

    @Override
    public long count() {
        return 0;
    }



    @Override
    public List<Borrow> findByUserIdAndStatus(Long userId, BorrowStatus status) {
        return List.of();
    }
}